﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace DAO
{
    public class CommonDAO
    {
        // static parameter
        private static SqlConnection conn = null;
      
        private SqlDataAdapter da;
        private DataSet dsTemp = new DataSet();

        public CommonDAO()
        {
            if (conn == null)
            {
                conn = new SqlConnection(Common.connectString);
            }
        }

        public SqlConnection GetConnection()
        {
            if (conn != null)
            {
                return conn;
            }
            else
            {
                conn = new SqlConnection(Common.connectString);
                return conn;
            }
        }
        // Execute stirng sqlcommand 
        public DataSet getInfoByCommand(string sqlCommand, string sqlTableName){
            SqlCommand cmd = new SqlCommand(sqlCommand, conn);
            da = new SqlDataAdapter(cmd);
            if(conn.State != ConnectionState.Open)
                conn.Open();
            // the second parameter is the table name which is to be changed.
            dsTemp.Clear();
            da.Fill(dsTemp, sqlTableName);
            conn.Close();
            return dsTemp;
        }

        // Execute a prepared command
        public DataSet executeCommand(SqlCommand cmd, string strTableName){
            da = new SqlDataAdapter(cmd);
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            dsTemp.Clear();
            da.Fill(dsTemp, strTableName);
            conn.Close();
            return dsTemp;
        }

        // to execute update and delete
        public int ExecuteNonQueryCommand(SqlCommand cmd){
            if (conn.State != ConnectionState.Open)
                conn.Open();
            cmd.Connection = conn;
            int iRet = cmd.ExecuteNonQuery();
            conn.Close();
            return iRet;
        }

        // Get a connection specified command
        public SqlCommand getCommand(string strCommand){
            SqlCommand cmd = new SqlCommand(strCommand, conn);
            return cmd;
        }
    }
}
